package kr.or.kead.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.or.kead.domain.Course;
import kr.or.kead.domain.InfoStudent;
import kr.or.kead.domain.ViewCourse;
import kr.or.kead.utils.MySqlCon;

public class DaoCourse implements DaoTable {

	@Override
	public int insertDao(Object obj) {
		Connection con = MySqlCon.getConn();
		Course course = (Course) obj;
		System.out.println(course);
		
		String sql ="insert into course(code, depart_code, subject, material, prof_code) values (?,?,?,?,?)";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, course.getCode());
			pstmt.setInt(2, course.getDepartCode());
			pstmt.setString(3, course.getSubject());
			pstmt.setString(4, course.getMaterial());
			pstmt.setInt(5, course.getProfCode());
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}finally{
			try {pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return 0;
	}

	@Override
	public int updateDao(Object obj) {
		Connection con = MySqlCon.getConn();
			
		Course course = (Course) obj;
		System.out.println(course);
		
		String sql = "update course set depart_code=?, subject=?, material=?, prof_code=? where code =?";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, course.getDepartCode());
			pstmt.setString(2, course.getSubject());
			pstmt.setString(3, course.getMaterial());
			pstmt.setInt(4, course.getProfCode());
			pstmt.setInt(5, course.getCode());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}finally{
			try {pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return 0;
	}

	@Override
	public int deleteDao(int regNo) {
		Connection con = MySqlCon.getConn();
		
		String sql = "delete from course where code = ?";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, regNo);
			pstmt.execute();
			System.out.println(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}finally{
			try {pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return 0;
	}

	@Override
	public ArrayList<Object> selectDao() {
		Connection con = MySqlCon.getConn();
		ArrayList<Object> courses = new ArrayList<>();
		String sql ="select * from view_course_depart_prof";
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);		
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				courses.add(new ViewCourse(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)));
				return courses;	
			}
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			try {rs.close();pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return null;
	}

	@Override
	public Object selectTable(int stdId) {
		Connection con = MySqlCon.getConn();
		
		
		String sql = "select code, depart_code, subject, material, prof_code from course where code = ?";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);		
			pstmt.setInt(1, stdId);
			
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				return new Course(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getInt(5));					
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {rs.close();pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return null;
	}

	@Override
	public ArrayList<String> selectTableAllList() {
		Connection con = MySqlCon.getConn();
		String sql = "select code, subject from course";
		ArrayList<String> lists = new ArrayList<>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				lists.add(rs.getInt(1)+":"+rs.getString(2));
				System.out.println(lists);				
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			try {rs.close();pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return lists;
	}

	@Override
	public Object selectTableByEmail(String email) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int updatePasswd(String pass, String email) {
		// TODO Auto-generated method stub
		return 0;
	}
	public int selectMaxCode(int departCode){
		Connection con = MySqlCon.getConn();
		String sql = "select max(code) from course where depart_code = ?";
		
		PreparedStatement pstmt = null;
		ResultSet rs =null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, departCode);
			
			rs = pstmt.executeQuery();
			if(rs.next()){
				return rs.getInt(1);
			}
						
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}finally{
			try {rs.close();pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return -1;
	}
	public Object selectTableAllListByProfCode(int prof_code){
		Connection con = MySqlCon.getConn();
		String sql ="select code, depart_code, subject, material, prof_code from course where prof_code =?";
		
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, prof_code);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				return new Course(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getInt(5));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}finally{
			try {rs.close();pstmt.close();con.close();} catch (SQLException e) {e.printStackTrace();}
		}
		return null;
	}
}
